const xlsx = require('xlsx');
const path = require('path');
const cloudbase = require("@cloudbase/node-sdk");
const dayjs = require('dayjs');
const { json } = require('stream/consumers');
const envId = 'cnycharts-4giqg3kn6179e262';
const fs = require('fs');

const secret = {
	secretId: 'AKID8X3tkbU4iVPPQOaxV3yZJ5ernnIrkbci',
	secretKey: 'EcE2GWlzGQhAeJ4lN7sr1If0gXME0gUG',
}
const app = cloudbase.init({
	...secret,
	env: envId,
	timeout: 300000
});
const db = app.database();
const today = dayjs().format('YYYY-MM-DD');

const dataRoot = "D:/_zytt/数据自动化"
const fileList = [
	"开通类指标/开通类指标.xlsx",
	"投诉类指标/投诉类指标.xlsx",
	"质量类指标/质差用户性能工单解决及时率.xlsx",
]

const gridMap = {
	"开发区": "茂南",
	"茂北": "茂南",
	"西城": "茂南",
	"油城北": "茂南",
	"油城东": "茂南",
	"油城南东": "茂南",
	"油城南西": "茂南",

	"滨海": "电白",
	"林头": "电白",
	"麻岗": "电白",
	"南海": "电白",
	"沙琅": "电白",
	"水东": "电白",

	"大井": "高州",
	"高城北": "高州",
	"高城南": "高州",
	"根子": "高州",
	"石鼓": "高州",
	"长坡": "高州",

	"合江": "化州",
	"河东": "化州",
	"化北": "化州",
	"化河西": "化州",
	"同庆": "化州",
	"杨梅": "化州",
	"北界": "信宜",
	"城北": "信宜",
	"城南": "信宜",
	"合水": "信宜",
	"钱排": "信宜",
	"朱砂": "信宜",
}

const toExcelDate = (date) => {
	// 将标准日期转为excel日期

	var dd = dayjs(date);
	var year = dd.year(),
		month = dd.month(),
		date = dd.date();
	var start = new Date(1900, 0, 1); // 1900年1月1日
	var end = new Date(year, month, date);
	var days = (
		(end.getTime() - start.getTime()) / (24 * 60 * 60 * 1000) +
		2
	).toFixed(0);

	return +days;
}


async function main() {
	// 处理数据
	const allData = fileList.map(filePath => {
		let dataArr = []
		const fullPath = path.join(dataRoot, filePath)
		console.log(`开始处理${fullPath}`)
		const workbook = xlsx.readFile(fullPath);
		const missSheets = ["部门统计", "个人统计"].filter(i => !workbook.SheetNames.includes(i))
		if (missSheets.length > 0) {
			console.log(`${fullPath}缺少${missSheets}`)
			return dataArr;
		}

		// console.log(workbook.Workbook.Names)
		const regionReg = /(茂南|电白|信宜|高州|化州|全市)/;
		const bumenSheet = workbook.Workbook.Names.find(i => i.Ref.indexOf("部门统计") >= 0)
		const gerenSheet = workbook.Workbook.Names.find(i => i.Ref.indexOf("个人统计") >= 0)

		const bumenRange = bumenSheet ? bumenSheet.Ref : "";
		const gerenRange = gerenSheet ? gerenSheet.Ref : "";

		// 根据范围读取数据
		dataArr = [bumenRange, gerenRange].map(targetRange => {

			if (targetRange) {
				// 解析范围字符串，这里假设是最简单的情况 A1:B2
				const targetRangeStr = targetRange.split("!")
				const s = targetRangeStr[1].replaceAll("$", "")
				const range = xlsx.utils.decode_range(s);
				const worksheet = workbook.Sheets[targetRangeStr[0]];

				const json = xlsx.utils.sheet_to_json(worksheet, {
					header: 1,
					range: range
				});
				const headers = json[0]; // 第一行是标题
				const rows = json.slice(1);

				const objArray = rows.map(row => {
					const obj = {};

					headers.forEach((h, index) => {
						// 去掉分子分母
						if (h.endsWith("分子") || h.endsWith("分母")) {
							return
						}
						obj[h] = row[index] || ""
						if (h === '部门') {
							if (regionReg.test(row[index])) {
								obj['区域'] = row[index]
							} else {
								obj['区域'] = gridMap[row[index]]
								obj['网格'] = row[index]
							}
						}
					})
					return obj;
				});

				return objArray;
			}
		})
		return dataArr;
	})

	let bumenObj = {}, renyuanObj = {};
	allData.forEach((arr) => {
		const [bumenArr, renyuanArr] = arr;
		bumenArr.forEach((item) => {
			if (!bumenObj[item['部门']]) {
				bumenObj[item['部门']] = {};
			}
			bumenObj[item['部门']] = { ...bumenObj[item['部门']], ...item };
		})
		renyuanArr.forEach((item) => {
			if (!bumenObj[item['姓名']]) {
				bumenObj[item['姓名']] = {};
			}
			bumenObj[item['姓名']] = { ...bumenObj[item['姓名']], ...item };
		})
	})

	const finalData = Object.keys(bumenObj).map(k => bumenObj[k])
		.concat(Object.keys(renyuanObj).map(k => renyuanObj[k]))
		.map(i => ({ ...i, batchId: today, "日期": toExcelDate(today) }))

	// 上传数据*******************************************************************************
	console.log("数据处理完成，开始上传数据")
	const collection = "bangdan";
	const deleteRs = await db.collection(collection).where({
		batchId: today
	}).remove();
	console.log("删除结果：", deleteRs)

	const insertRs = await db.collection(collection).add(finalData);
	console.log("数据更新结果：", insertRs.ids && insertRs.ids.length)

	// 上传文件
	for (const filePath of fileList) {
		try {
			const fullPath = path.join(dataRoot, filePath)
			const fileName = path.basename(filePath, ".xlsx")
			const uploadRs = await app
				.uploadFile({
					cloudPath: `bangdan/${fileName}.${today}.xlsx`,
					fileContent: fs.createReadStream(fullPath)
				})

			if (uploadRs.fileID) {
				console.log(`文件上传成功【${fullPath}】`);
			} else {
				console.log(`文件上传失败【${fullPath}】`);
			}

		} catch (error) {
			console.log(`文件上传失败【${fullPath}】`);
			console.error(error);
		}
	}
}

main();

